<?php
require_once 'z.php';

$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$donation_allocations = new Zend_Db_Table(array('name' => 'donation_allocations', 'primary' => 'DonationAllocationId'));

$sponsors = getSponsors();
$masters = getMasters($app_config->$argv[1]->param->sponsorship->auto_sp_margin);

$datetime = date("Y-m-d H:i:s");
$db->beginTransaction();
try {
    foreach($masters as $lm) {
        while(($lm['Pending'] > 0)&&($pid = getMaxPriorityId($sponsors,$lm['LoanMasterId'])))
        {
            $k = $sponsors[$pid]['Donations'] - $sponsors[$pid]['WriteOffs'];
            if($sponsors[$pid]['Funds'] > 0) {
                $amt = min($lm['Pending'],$sponsors[$pid]['Funds']);
                $lm['Pending'] = $lm['Pending'] - $amt;
                $sponsors[$pid]['Balance'] = $sponsors[$pid]['Balance'] + $amt;
                $sponsors[$pid]['Funds'] = $sponsors[$pid]['Funds'] - $amt;
                $sponsors[$pid]['Priority'] = 1 - ($sponsors[$pid]['Balance']/$k);
                $donation_allocations->insert(array(
                    'LoanMasterId' => $lm['LoanMasterId'],
                    'SponsorId' => $pid,
                    'Donation' => $amt,
                    'Datetime' => $datetime
                ));
            }
        }
    }
} catch  (Zend_Exception $e) {
    $db->rollBack();
    echo $e->getMessage();
    exit;
}
$db->commit();

/*
 * Includes support for the WomenOnly sponsor field
 */
function getMaxPriorityId($s,$loanMasterId)
{
    $db = Zend_Db_Table_Abstract::getDefaultAdapter();
    $male = $db->fetchAll('select c.Gender from loans l, clients c 
        where l.LoanMasterId = ? and c.ClientId = l.ClientId and c.Gender = "Male"', $loanMasterId);
    
    $pid = key($s);
    $pval = $s[$pid]['Priority'];
    foreach($s as $id => $data) {
        if ($data['Priority'] > $pval) { 
            if(($data['WomenOnly'] == 'No' )||(count($male) == 0)) {
                $pid = $id;
                $pval = $data['Priority'];	
            }
        }
    }
    return $pid;
}

function getSponsors() 
{
    $db = Zend_Db_Table_Abstract::getDefaultAdapter();
    $sps = $db->fetchAssoc('
        select 
            da.SponsorId,
            dn.Donations,
            round(sum(if(al.BalancePrincipal,al.BalancePrincipal*(da.Donation/lm.Amount),0)),2) Balance,
            round(sum(if(lwo.Principal,lwo.Principal*(da.Donation/lm.Amount),0)),2) WriteOffs
        from (donation_allocations da, loan_masters lm, loans l)
        left join active_loans al on al.LoanId = l.LoanId
        left join loan_write_offs lwo on lwo.LoanId = l.LoanId
        left join (select SponsorId, sum(ConvAmount) Donations from donations group by SponsorId) dn on dn.SponsorId = da.SponsorId
        where
            lm.Status = "Disbursed" and
            lm.LoanMasterId = da.LoanMasterId and
            l.LoanMasterId = da.LoanMasterId
        group by da.SponsorId');
        
    $sp = new Zend_Db_Table(array('name' => 'view_sponsors', 'primary' => 'SponsorId'));
    $sponsors = $sp->fetchAll();

    $s = array();
    foreach($sponsors as $sponsor) {
        $donations = $sps[$sponsor->SponsorId]['Donations'];
        $balance = $sps[$sponsor->SponsorId]['Balance'];
        $woffs = $sps[$sponsor->SponsorId]['WriteOffs'];
        $funds = $donations - $balance - $woffs;
        if($funds > 0) {
            $s[$sponsor->SponsorId]['WomenOnly'] = $sponsor['WomenOnly'];
            $s[$sponsor->SponsorId]['Donations'] = $donations;
            $s[$sponsor->SponsorId]['Balance']   = $balance;
            $s[$sponsor->SponsorId]['WriteOffs'] = $woffs;
            $s[$sponsor->SponsorId]['Funds']     = $funds;
            $s[$sponsor->SponsorId]['Priority']  = 1 - ($balance/($donations-$woffs));        
        }
    }
    return $s; 
}

function getMasters($margin)
{ 
    $db = Zend_Db_Table_Abstract::getDefaultAdapter();
    $date = date('Y-m-d');
    return $db->fetchAssoc('
        select distinct 
            lm.LoanMasterId, 
            1 + ? - datediff(?,ckd.Date) TimeLeft, 
            coalesce(rc.Donations,0.00) Donations, 
            lm.Amount - coalesce(rc.Donations,0.00) Pending,
            lm.LocationId
        from 
        (
            loan_masters lm, programs p,
            (
                select l.LoanMasterId, lsc.Date 
                from loan_status_changes lsc, loans l 
                where 
                    l.LoanId = lsc.LoanId and 
        	    lsc.Status = "Disbursed" and 
        	    lsc.Date = date_add(?, interval -(? + 1) day)
            ) ckd 
        ) 
        left join 
        (
            select LoanMasterId, sum(Donation) Donations from donation_allocations group by LoanMasterId
        ) rc on rc.LoanMasterId = lm.LoanMasterId 
        where 
            lm.BorrowerType = "B" and 
            lm.Status = "Disbursed" and 
            p.ProgramId = lm.ProgramId and
            p.PublicSponsorship = "Yes" and
            ckd.LoanMasterId = lm.LoanMasterId and 
            coalesce(rc.Donations,0.00) < lm.Amount 
        order by 
            lm.LoanMasterId',array($margin,$date,$date,$margin));
}

?>